﻿using System;
using SmartSiteManagementSystem.IRepository.Admin;
using SmartSiteManagementSystem.Models.Admin;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;

namespace SmartSiteManagementSystem.Repository.Admin
{
    public class AdminMenuInfoRepository : IAdminMenuInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
         create table `admin_menu`(
    `menu_id` int not null auto_increment primary key,
	`parentId` int not null,
	`menu_name` varchar(100) not null,
	`is_parent` bit not null,
	`url` varchar(500) null,
	`relevance_url` text null,
	`help_text` text null,
	`sort_order` int not null,
	`is_show` bit not null,
	`is_stop` bit not null default 0
)default charset=utf8;
         */
        #endregion
        private IDbConnection _readConn;
        private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public bool Delete(int AdminMenuId, int parentId)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 删除菜单和关联数据
        /// </summary>
        /// <param name="adminMenuId"></param>
        /// <returns></returns>
        public bool DeleteMenuAndRelData(int adminMenuId)
        {
            bool back = false;
            using (_writeConn = DBConectionHelper.GetInstance().GetWriteConn())
            using (IDbTransaction transaction = _writeConn.BeginTransaction())
            {
                try
                {
                   
                    string deleteAdminGroupBtnRelSql = "delete from admin_group_btnrel where admin_menuid=@admin_menuid";
                    DynamicParameters deleteAdminGroupBtnRelSqlPars = new DynamicParameters();
                    deleteAdminGroupBtnRelSqlPars.Add("admin_menuid", adminMenuId);
                    _writeConn.Execute(deleteAdminGroupBtnRelSql, deleteAdminGroupBtnRelSqlPars, transaction);

                    
                    string deleteAdminPageBtnSql = "delete from admin_pagebtn where admin_menu_id=@admin_menu_id";
                    DynamicParameters deleteAdminPageBtnSqlPars = new DynamicParameters();
                    deleteAdminPageBtnSqlPars.Add("admin_menu_id", adminMenuId);
                    _writeConn.Execute(deleteAdminPageBtnSql, deleteAdminPageBtnSqlPars, transaction);



                    string deleteAdminMenuSql = "delete from admin_menu where menu_id=@menu_id";
                    DynamicParameters deleteAdminMenuSqlPars = new DynamicParameters();
                    deleteAdminMenuSqlPars.Add("menu_id", adminMenuId);
                    _writeConn.Execute(deleteAdminMenuSql, deleteAdminMenuSqlPars, transaction);
                    transaction.Commit();
                    back= true;
                }
                catch
                {
                    transaction.Rollback();
                    back= false;
                }

            }
            return back;
        }

        public AdminMenuInfo Get(Hashtable param)
        {
            AdminMenuInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select menu_id as AdminMenuId,parentId as ParentId,menu_name as MenuName,is_parent as IsParent ,url as Url,relevance_url as RelevanceUrl,help_text as HelpText,sort_order as SortOrder,is_show as ShowFlag,is_stop as StopFlag from admin_menu";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition =new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("menu_id"))
                    {
                        pars.Add("menu_id", param["menu_id"]);
                        whereCondition.Append(" and menu_id =@menu_id");
                    }
                    if (param.ContainsKey("parentId"))
                    {
                        pars.Add("parentId", param["parentId"]);
                        whereCondition.Append(" and parentId =@parentId");
                    }
                    if (param.ContainsKey("menu_name"))
                    {
                        pars.Add("menu_name", param["menu_name"]);
                        whereCondition.Append(" and menu_name =@menu_name");
                    }
                    if (param.ContainsKey("is_parent"))
                    {
                        pars.Add("is_parent", param["is_parent"]);
                        whereCondition.Append(" and is_parent =@is_parent");
                    }
                    if (param.ContainsKey("is_show"))
                    {
                        pars.Add("is_show", param["is_show"]);
                        whereCondition.Append(" and is_show =@is_show");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                }
                whereCondition.Append(" limit 1");
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<AdminMenuInfo>(execSql, pars);
            }
            return back;
        }

        public int GetCount(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public IList<AdminMenuInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {
            IList<AdminMenuInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select menu_id as AdminMenuId,parentId as ParentId,menu_name as MenuName,is_parent as IsParent ,url as Url,relevance_url as RelevanceUrl,help_text as HelpText,sort_order as SortOrder,is_show as ShowFlag,is_stop as StopFlag from admin_menu";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("menu_id"))
                    {
                        pars.Add("menu_id", param["menu_id"]);
                        whereCondition.Append(" and menu_id =@menu_id");
                    }
                    if (param.ContainsKey("parentId"))
                    {
                        pars.Add("parentId", param["parentId"]);
                        whereCondition.Append(" and parentId =@parentId");
                    }
                    if (param.ContainsKey("menu_name"))
                    {
                        pars.Add("menu_name", param["menu_name"]);
                        whereCondition.Append(" and menu_name =@menu_name");
                    }
                    if (param.ContainsKey("is_parent"))
                    {
                        pars.Add("is_parent", param["is_parent"]);
                        whereCondition.Append(" and is_parent =@is_parent");
                    }
                    if (param.ContainsKey("is_show"))
                    {
                        pars.Add("is_show", param["is_show"]);
                        whereCondition.Append(" and is_show =@is_show");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if(param.ContainsKey("AdminUserId"))
                    {
                        whereCondition.Append(" and  menu_id in (select menu_id from admin_user_group_menu where group_id in(select user_group_id from admin_user_group_relation where admin_user_id =@AdminUserId and user_group_id in (select group_id from admin_group where is_stop = 0)))");
                        pars.Add("AdminUserId", param["AdminUserId"]);
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by sort_order asc, menu_id asc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<AdminMenuInfo>(execSql, pars).AsList<AdminMenuInfo>();
            }
            return back;
        }

        public IList<AdminMenuInfo> GetList(Hashtable param)
        {
            IList<AdminMenuInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select menu_id as AdminMenuId,parentId as ParentId,menu_name as MenuName,is_parent as IsParent ,url as Url,relevance_url as RelevanceUrl,help_text as HelpText,sort_order as SortOrder,is_show as ShowFlag,is_stop as StopFlag from admin_menu";
            using (_readConn = DBConectionHelper.GetInstance().GetReadConn())
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("menu_id"))
                    {
                        pars.Add("menu_id", param["menu_id"]);
                        whereCondition.Append("and menu_id =@menu_id");
                    }
                    if (param.ContainsKey("parentId"))
                    {
                        pars.Add("parentId", param["parentId"]);
                        whereCondition.Append(" and parentId =@parentId");
                    }
                    if (param.ContainsKey("menu_name"))
                    {
                        pars.Add("menu_name", param["menu_name"]);
                        whereCondition.Append(" and menu_name =@menu_name");
                    }
                    if (param.ContainsKey("is_parent"))
                    {
                        pars.Add("is_parent", param["is_parent"]);
                        whereCondition.Append(" and is_parent =@is_parent");
                    }
                    if (param.ContainsKey("is_show"))
                    {
                        pars.Add("is_show", param["is_show"]);
                        whereCondition.Append(" and is_show =@is_show");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("AdminUserId"))
                    {
                        whereCondition.Append(" and  menu_id in (select menu_id from admin_user_group_menu where group_id in(select user_group_id from admin_user_group_relation where admin_user_id =@AdminUserId and user_group_id in (select group_id from admin_group where is_stop = 0)))");
                        pars.Add("AdminUserId", param["AdminUserId"]);
                    }

                }

                whereCondition.Append(" order by sort_order asc, menu_id asc");

                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<AdminMenuInfo>(execSql, pars).AsList<AdminMenuInfo>();
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(AdminMenuInfo obj)
        {
            int back = 0;
            string sql = @"INSERT INTO admin_menu(
parentId,
menu_name,
is_parent,
url,
relevance_url,
help_text,
sort_order,
is_show,
is_stop)
VALUES
(@parentId,
@menu_name,
@is_parent,
@url,
@relevance_url,
@help_text,
@sort_order,
@is_show,
@is_stop);select last_insert_id();";

            using (_writeConn = DBConectionHelper.GetInstance().GetWriteConn())
            {

                int backint = _writeConn.QueryFirst<int>(sql, new
                {
                    parentId = obj.ParentId,
                    menu_name = obj.MenuName,
                    is_parent = obj.IsParent,
                    url = obj.Url,
                    relevance_url = obj.RelevanceUrl,
                    help_text = obj.HelpText,
                    sort_order = obj.SortOrder,
                    is_show = obj.ShowFlag,
                    is_stop = obj.StopFlag
                });
                back = backint;
            }
            return back;
        }

        public bool Update(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public bool Update(AdminMenuInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"UPDATE admin_menu SET
parentId = @parentId,
menu_name =@menu_name,
is_parent = @is_parent,
url= @url,
relevance_url =@relevance_url,
help_text = @help_text,
sort_order = @sort_order,
is_show = @is_show,
is_stop = @is_stop
WHERE menu_id = @menu_id;";
            #endregion
            using (_writeConn = DBConectionHelper.GetInstance().GetWriteConn())
            {

                int backint = _writeConn.Execute(sql, new
                {
                    menu_id = obj.AdminMenuId,
                    parentId = obj.ParentId,
                    menu_name = obj.MenuName,
                    is_parent = obj.IsParent,
                    url = obj.Url,
                    relevance_url = obj.RelevanceUrl,
                    help_text = obj.HelpText,
                    sort_order = obj.SortOrder,
                    is_show = obj.ShowFlag,
                    is_stop = obj.StopFlag
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        public bool UpdateHelpText(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public bool UpdateIsParentByMenuId(bool isParent, int adminMenuId)
        {
            throw new NotImplementedException();
        }
    }
}

